drop table if exists t1, t2;
CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 5;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ENGINE= ARCHIVE;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, b VARCHAR(200), c TEXT) ENGINE=InnoDB PARTITION BY HASH(a) PARTITIONS 1;
CREATE FULLTEXT INDEX msg ON t1 (b, c);
DROP TABLE t1;
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, b VARCHAR(200), c TEXT, FULLTEXT (b, c)) ENGINE=InnoDB PARTITION BY HASH(a) PARTITIONS 1;
CREATE TABLE t1 ( i INT ) ENGINE=InnoDB PARTITION BY RANGE (i) (PARTITION p3 VALUES LESS THAN (3), PARTITION p5 VALUES LESS THAN (5), PARTITION pMax VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
CHECKSUM TABLE t1;
ALTER TABLE t1 CHECKSUM = 1;
CHECKSUM TABLE t1 EXTENDED;
CHECKSUM TABLE t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 ( i INT ) ENGINE=InnoDB;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6);
CHECKSUM TABLE t1;
ALTER TABLE t1 CHECKSUM = 1;
CHECKSUM TABLE t1 EXTENDED;
CHECKSUM TABLE t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b varchar(64), PRIMARY KEY (a), KEY (b)) ENGINE = InnoDB PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 10 (PARTITION pNeg VALUES LESS THAN (0), PARTITION p0 VALUES LESS THAN (1000), PARTITION pMAX VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (-1, 'Only negative pk value');
INSERT INTO t1 VALUES (0, 'Mod Zero'), (1, 'One'), (2, 'Two'), (3, 'Three'), (10, 'Zero'), (11, 'Mod One'), (12, 'Mod Two'), (13, 'Mod Three'), (20, '0'), (21, '1'), (22, '2'), (23, '3'), (4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9');
INSERT INTO t1 SELECT a + 30, b FROM t1 WHERE a >= 0;
ANALYZE TABLE t1;
EXPLAIN SELECT b FROM t1 WHERE b between 'L' and 'N' AND a > -100;
DROP TABLE t1;
CREATE TABLE t1 (a INT, KEY(a)) ENGINE = InnoDB PARTITION BY KEY (a) PARTITIONS 1;
SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0) ORDER BY a;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL AUTO_INCREMENT, c2 varchar(40) not null default '', c3 datetime not  NULL, PRIMARY KEY (c1,c3), KEY partidx(c3)) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(c3)) (PARTITION p200912 VALUES LESS THAN (to_days('2010-01-01')), PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')), PARTITION p201912 VALUES LESS THAN MAXVALUE);
insert into t1(c2,c3) values ("Test row",'2010-01-01 00:00:00');
SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
SELECT count(*) FROM t1 p where c3 in (SELECT c3 FROM t1 t WHERE t.c3 < TIMESTAMP'2011-04-26 19:19:44'  AND t.c3 > TIMESTAMP'2011-04-26 19:18:44') ;
DROP TABLE t1;
SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency;
SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay;
SET GLOBAL innodb_thread_concurrency = 2;
CREATE TABLE t1 (user_num BIGINT, hours SMALLINT, KEY user_num (user_num)) ENGINE = InnoDB    PARTITION BY RANGE COLUMNS (hours) (PARTITION hour_003 VALUES LESS THAN (3), PARTITION hour_004 VALUES LESS THAN (4), PARTITION hour_005 VALUES LESS THAN (5), PARTITION hour_last VALUES LESS THAN (MAXVALUE));
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
BEGIN;
SELECT COUNT(*) FROM t1;
ALTER TABLE t1 REORGANIZE PARTITION hour_003, hour_004 INTO (PARTITION oldest VALUES LESS THAN (4));
SELECT COUNT(*) FROM t1;
COMMIT;
SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency;
SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay;
DROP TABLE t1;
CREATE TABLE t1 ( id INT AUTO_INCREMENT NOT NULL, name CHAR(50) NOT NULL, myDate DATE NOT NULL, PRIMARY KEY (id, myDate), INDEX idx_date (myDate) ) ENGINE=InnoDB PARTITION BY RANGE ( TO_DAYS(myDate) ) ( PARTITION p0 VALUES LESS THAN (734028), PARTITION p1 VALUES LESS THAN (734029), PARTITION p2 VALUES LESS THAN (734030), PARTITION p3 VALUES LESS THAN MAXVALUE ) ;
INSERT INTO t1 VALUES  (NULL, 'Lachlan', '2009-09-13'), (NULL, 'Clint', '2009-09-13'), (NULL, 'John', '2009-09-14'), (NULL, 'Dave', '2009-09-14'), (NULL, 'Jeremy', '2009-09-15'), (NULL, 'Scott', '2009-09-15'), (NULL, 'Jeff', '2009-09-16'), (NULL, 'Joe', '2009-09-16');
SET AUTOCOMMIT=0;
SELECT * FROM t1 FOR UPDATE;
UPDATE t1 SET name = 'Mattias' WHERE id = 7;
SELECT * FROM t1 WHERE id = 7;
SET lock_wait_timeout = 1;
ALTER TABLE t1 DROP PARTITION p3;
SHOW WARNINGS;
SELECT * FROM t1;
COMMIT;
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL) ENGINE = InnoDB PARTITION BY RANGE(a) (PARTITION p10 VALUES LESS THAN (10), PARTITION p30 VALUES LESS THAN (30), PARTITION p50 VALUES LESS THAN (50), PARTITION p70 VALUES LESS THAN (70), PARTITION p90 VALUES LESS THAN (90));
INSERT INTO t1 VALUES (10),(30),(50);
INSERT INTO t1 VALUES (70);
INSERT INTO t1 VALUES (80);
INSERT INTO t1 VALUES (89);
INSERT INTO t1 VALUES (90);
INSERT INTO t1 VALUES (100);
insert INTO t1 VALUES (110);
ANALYZE TABLE t1;
EXPLAIN SELECT * FROM t1 WHERE a > 90;
EXPLAIN SELECT * FROM t1 WHERE a >= 90;
EXPLAIN SELECT * FROM t1 WHERE a = 90;
EXPLAIN SELECT * FROM t1 WHERE a = 89;
EXPLAIN SELECT * FROM t1 WHERE a >= 89;
EXPLAIN SELECT * FROM t1 WHERE a > 89;
EXPLAIN SELECT * FROM t1 WHERE a = 100;
EXPLAIN SELECT * FROM t1 WHERE a >= 100;
EXPLAIN SELECT * FROM t1 WHERE a > 100;
DROP TABLE t1;
CREATE TABLE t2 ( id INT, PRIMARY KEY (id) ) ENGINE=InnoDB ;
CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT, parent_id INT DEFAULT NULL, PRIMARY KEY (id), KEY parent_id (parent_id) ) ENGINE=InnoDB;
ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 1;
ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
ALTER TABLE t1 PARTITION BY HASH (id) PARTITIONS 2;
ALTER TABLE t1 ADD CONSTRAINT test_ibfk_1 FOREIGN KEY (parent_id) REFERENCES t2 (id);
DROP TABLE t1, t2;
create table t1 (a varchar(5), b int signed, c varchar(10), d datetime) partition by range columns(b,c) subpartition by hash(to_seconds(d)) ( partition p0 values less than (2, 'b'), partition p1 values less than (4, 'd'), partition p2 values less than (10, 'za'));
insert into t1 values ('a', 3, 'w', '2001-10-27 04:34:00');
insert into t1 values ('r', 7, 'w', '2001-10-27 05:34:00');
insert into t1 values ('g', 10, 'w', '2001-10-27 06:34:00');
update t1 set a = 'c' where a > 'f';
drop table t1;
create table t1 (a varchar(5)) engine=innodb partition by range columns(a) ( partition p0 values less than ('m'), partition p1 values less than ('za'));
insert into t1 values  ('j');
update t1 set a = 'z' where (a >= 'j');
drop table t1;
create table t1 (a int not null, b datetime not null, primary key (a,b)) engine=innodb partition by range (to_days(b)) subpartition by hash (a) subpartitions 2 ( partition p0 values less than (to_days('2009-01-01')), partition p1 values less than (to_days('2009-02-01')), partition p2 values less than (to_days('2009-03-01')), partition p3 values less than maxvalue);
alter table t1 reorganize partition p1,p2 into ( partition p2 values less than (to_days('2009-03-01')));
drop table t1;
CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB  PARTITION BY RANGE(id) (  PARTITION p0 VALUES LESS THAN (5),  PARTITION p1 VALUES LESS THAN (10),  PARTITION p2 VALUES LESS THAN MAXVALUE  );
INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10), (11,11);
SET @old_transaction_isolation := @@session.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET autocommit = 0;
UPDATE t1 SET DATA = data*2 WHERE id = 3;
UPDATE t1 SET data = data*2 WHERE data = 2;
SET @@session.transaction_isolation = @old_transaction_isolation;
SET autocommit = default;
DROP TABLE t1;
CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a), INDEX (b)) ENGINE InnoDB PARTITION BY HASH(a) PARTITIONS 3;
INSERT INTO t1 VALUES (0,0),(4,0),(2,0);
SELECT a FROM t1 WHERE b = 0 ORDER BY a ASC;
SELECT a FROM t1 WHERE b = 0 ORDER BY a DESC;
ALTER TABLE t1 DROP INDEX b;
